﻿using System;
using System.Collections.Generic;
using System.Data;
using MySql.Data.MySqlClient;

namespace footwear
{
    public class ShoeInfoDao
    {
        public List<ShoeInfo> GetAllShoeItem()
        {
            try
            {
                DbConnect.CloseConnection();
            }
            catch { }
            DbConnect.OpenConnection();
            var list = new List<ShoeInfo>();
            try
            {
                var myCommand = new MySqlCommand("GetListShoes;", DbConnect.Connection) { CommandType = CommandType.StoredProcedure };
                MySqlDataReader reader = myCommand.ExecuteReader();
                while (reader.Read())
                {
                    var shoe = new ShoeInfo
                                   {
                                       Code = reader["Code"].ToString(),
                                       Name = reader["Name"].ToString(),
                                       NameEng = reader["NameEng"].ToString(),
                                       Price = reader["Price"].ToString(),
                                       ImageSource = reader["Image"].ToString()
                                   };
                    list.Add(shoe);
                }
                DbConnect.CloseConnection();
            }
            catch (MySqlException)
            {
                DbConnect.CloseConnection();
            }
            return list;
        }

        public ShoeInfo GetSpecificShoe(string code)
        {
            try
            {
                DbConnect.CloseConnection();
            }
            catch { }
            DbConnect.OpenConnection();
            var shoeInfo = new ShoeInfo();
            try
            {
                var myCommand = new MySqlCommand("GetSpecificShoe;", DbConnect.Connection) { CommandType = CommandType.StoredProcedure };
                myCommand.Parameters.AddWithValue("?code", code);
                myCommand.Parameters["?code"].Direction = ParameterDirection.Input;
                MySqlDataReader reader = myCommand.ExecuteReader();
                while (reader.Read())
                {
                    shoeInfo.Code = code;
                    shoeInfo.ImageSource = reader["Image"].ToString();
                    shoeInfo.Name = reader["Name"].ToString();
                    shoeInfo.Price = reader["Price"].ToString();
                    shoeInfo.ShowIndex = Convert.ToInt32(reader["ShowIndex"].ToString());
                    shoeInfo.ShoeSubInfo.Type = reader["Type"].ToString();
                    shoeInfo.ShoeSubInfo.Size = reader["Size"].ToString();
                    shoeInfo.ShoeSubInfo.Color = reader["Color"].ToString();
                    shoeInfo.ShoeSubInfo.Description = reader["Description"].ToString();
                    shoeInfo.ShoeSubInfo.LeftImageSource = reader["Left_image"].ToString();
                    shoeInfo.ShoeSubInfo.RightImageSource = reader["Right_image"].ToString();
                    shoeInfo.ShoeSubInfo.FrontImageSource = reader["Front_image"].ToString();
                    shoeInfo.ShoeSubInfo.BackImageSource = reader["Back_image"].ToString();
                    shoeInfo.ShoeSubInfo.TopImageSource = reader["Top_image"].ToString();
                }
                DbConnect.CloseConnection();
                return shoeInfo;
            }
            catch (MySqlException)
            {
                DbConnect.CloseConnection();
                return shoeInfo;
            }
        }

        public List<ShoeInfo> GetListShoesByCategory(string categoryType)
        {
            try
            {
                DbConnect.CloseConnection();
            }
            catch { }
            DbConnect.OpenConnection();
            var list = new List<ShoeInfo>();
            try
            {
                string query =
                    "SELECT shoes_detail.Code, shoes_detail.Name, shoes_detail.Price, shoes_detail.Image, shoes_detail.ShowIndex, category.Type ";
                query += "FROM shoes_detail, category ";
                query += "WHERE category.Id = shoes_detail.TypeId;";
                var myCommand = new MySqlCommand(query, DbConnect.Connection);
                MySqlDataReader reader = myCommand.ExecuteReader();
                while (reader.Read())
                {
                    if (reader["Type"].ToString() == categoryType)
                    {
                        var shoe = new ShoeInfo
                        {
                            Code = reader["Code"].ToString(),
                            Name = reader["Name"].ToString(),
                            Price = reader["Price"].ToString(),
                            ShowIndex = Convert.ToInt32(reader["ShowIndex"].ToString()),
                            ImageSource = reader["Image"].ToString()
                        };
                        list.Add(shoe);
                    }
                }
                DbConnect.CloseConnection();
            }
            catch (MySqlException)
            {
                DbConnect.CloseConnection();
            }
            return list;
        }

        public List<ShoeInfo> GetListShoesShowIndexByCategory(string categoryType)
        {
            try
            {
                DbConnect.CloseConnection();
            }
            catch { }
            DbConnect.OpenConnection();
            var list = new List<ShoeInfo>();
            try
            {
                string query =
                    "SELECT shoes_detail.Code, shoes_detail.Name, shoes_detail.Price, shoes_detail.Image, shoes_detail.ShowIndex, category.Type ";
                query += "FROM shoes_detail, category ";
                query += "WHERE category.Id = shoes_detail.TypeId;";
                var myCommand = new MySqlCommand(query, DbConnect.Connection);
                MySqlDataReader reader = myCommand.ExecuteReader();
                while (reader.Read())
                {
                    if (Convert.ToInt32(reader["ShowIndex"].ToString()) == 1 && reader["Type"].ToString() == categoryType)
                    {
                        var shoe = new ShoeInfo
                        {
                            Code = reader["Code"].ToString(),
                            Name = reader["Name"].ToString(),
                            Price = reader["Price"].ToString(),
                            ShowIndex = Convert.ToInt32(reader["ShowIndex"].ToString()),
                            ImageSource = reader["Image"].ToString()
                        };
                        list.Add(shoe);
                    }
                }
                DbConnect.CloseConnection();
            }
            catch (MySqlException)
            {
                DbConnect.CloseConnection();
            }
            return list;
        }

        public bool DeleteProductByCode(string code)
        {
            try
            {
                DbConnect.CloseConnection();
            }
            catch { }
            DbConnect.OpenConnection();
            try
            {
                string query = "DELETE FROM shoes_image WHERE ShoeCode=?code; DELETE FROM shoes_detail WHERE Code=?code;";
                var myCommand = new MySqlCommand(query, DbConnect.Connection);
                myCommand.Parameters.AddWithValue("code", code);
                try
                {
                    myCommand.ExecuteNonQuery();
                    DbConnect.CloseConnection();
                    return true;
                }
                catch
                {
                    DbConnect.CloseConnection();
                    return false;
                }
            }
            catch (MySqlException)
            {
                return false;
            }
        }

        public bool AddProduct(string code, string name, string price, string imageSource, string showIndex, string typeId, string size, string color, string description, string leftImage, string rightImage, string frontImage, string backImage, string topImage)
        {
            try
            {
                DbConnect.CloseConnection();
            }
            catch { }
            DbConnect.OpenConnection();
            try
            {
                string query =
                    "INSERT INTO shoes_detail (Code, Name, Size, Price, Color, Image, Description, TypeId, ShowIndex) ";
                query += "VALUES (?code, ?name, ?size, ?price, ?color, ?image, ?description, ?typeId, ?showIndex); ";
                query +=
                    "INSERT INTO shoes_image (ShoeCode, Left_Image, Right_Image, Front_Image, Back_Image, Top_Image) ";
                query += "VALUES (?code, ?leftImage, ?rightImage, ?frontImage, ?backImage, ?topImage); ";
                var myCommand = new MySqlCommand(query, DbConnect.Connection);
                myCommand.Parameters.AddWithValue("code", code);
                myCommand.Parameters.AddWithValue("name", name);
                myCommand.Parameters.AddWithValue("size", size);
                myCommand.Parameters.AddWithValue("price", price);
                myCommand.Parameters.AddWithValue("color", color);
                myCommand.Parameters.AddWithValue("image", imageSource);
                myCommand.Parameters.AddWithValue("description", description);
                myCommand.Parameters.AddWithValue("typeId", typeId); 
                myCommand.Parameters.AddWithValue("showIndex", showIndex);
                myCommand.Parameters.AddWithValue("leftImage", leftImage);
                myCommand.Parameters.AddWithValue("rightImage", rightImage);
                myCommand.Parameters.AddWithValue("frontImage", frontImage);
                myCommand.Parameters.AddWithValue("backImage", backImage);
                myCommand.Parameters.AddWithValue("topImage", topImage);
                try
                {
                    myCommand.ExecuteNonQuery();
                    DbConnect.CloseConnection();
                    return true;
                }
                catch
                {
                    DbConnect.CloseConnection();
                    return false;
                }
            }
            catch (MySqlException)
            {
                return false;
            }
        }

        public bool UpdateProductByCode(string code, string name, string price, string imageSource, string showIndex, string typeId, string size, string color, string description, string leftImage, string rightImage, string frontImage, string backImage, string topImage)
        {
            try
            {
                DbConnect.CloseConnection();
            }
            catch { }
            DbConnect.OpenConnection();
            try
            {
                string query =
                    "UPDATE shoes_detail SET Name=?name, Size=?size, Price=?price, Color=?color, Image=?image, Description=?description, TypeId=?typeId, ShowIndex=?showIndex WHERE Code=?code; ";
                query += "UPDATE shoes_image SET Left_image=?leftImage, Right_image=?rightImage, Front_image=?frontImage, Back_image=?backImage, Top_image=?topImage WHERE ShoeCode=?code;";
                var myCommand = new MySqlCommand(query, DbConnect.Connection);
                myCommand.Parameters.AddWithValue("code", code);
                myCommand.Parameters.AddWithValue("name", name);
                myCommand.Parameters.AddWithValue("size", size);
                myCommand.Parameters.AddWithValue("price", price);
                myCommand.Parameters.AddWithValue("color", color);
                myCommand.Parameters.AddWithValue("image", imageSource);
                myCommand.Parameters.AddWithValue("description", description);
                myCommand.Parameters.AddWithValue("typeId", typeId);
                myCommand.Parameters.AddWithValue("showIndex", showIndex);
                myCommand.Parameters.AddWithValue("leftImage", leftImage);
                myCommand.Parameters.AddWithValue("rightImage", rightImage);
                myCommand.Parameters.AddWithValue("frontImage", frontImage);
                myCommand.Parameters.AddWithValue("backImage", backImage);
                myCommand.Parameters.AddWithValue("topImage", topImage);
                try
                {
                    myCommand.ExecuteNonQuery();
                    DbConnect.CloseConnection();
                    return true;
                }
                catch
                {
                    DbConnect.CloseConnection();
                    return false;
                }
            }
            catch (MySqlException)
            {
                return false;
            }
        }
    }
}